package DRC;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.poi.hssf.usermodel.*;

import DRCOMM.DRHTTP_API;
import DRCOMM.DR_SESSION;
import DRCOMM.FJW_URI;
import DRCOMM.HttpRequestFormResolver;
import DRCOMM.dr_ajax_answer;
import DRCOMM.record_data_set;

public class dataItem2channel{

    public int dataItem2channel_id;
    public int channel_id=-1;
    public int dataItem_id=-1;
    public int slave_id=-1;
    public int data_index=-1;
    public String label="";
    public Date create_time=null;
    public dataItem4collect d4c=null;
    public DRCDB drcdb;
    public static ArrayList<channel> channels = null;
    public static ArrayList<channel4collect> channels4collect = null;

    public dataItem2channel(DRCDB drcdb) {
        this.drcdb = drcdb;
    }

    public dataItem2channel(int dataItem2channel_id, int channel_id, int dataItem_id, String label, Date create_time) {
        this.dataItem2channel_id = dataItem2channel_id;
        this.channel_id = channel_id;
        this.dataItem_id = dataItem_id;
        this.label = label;
        this.create_time = create_time;
    }

    public dataItem2channel(DRCDB drcdb, int channel_id, int slave_id,
                            int data_index, String label, int data_type) {
        this.drcdb = drcdb;
        this.channel_id = channel_id;
        this.slave_id = slave_id;
        this.data_index = data_index;
        this.label = label;
    }

    public static record_data_set readLocalDataItem(DRCDB drcdb, int channel_id, int slave_id, int register_type,
                                                    int data_index, int data_count) {
        String sqlstr = "select protocol from channel4collect";
        sqlstr += String.format(" where channel_id=%d", channel_id);
        record_data_set rds_channel4collect = drcdb.executeQuerySQL(sqlstr);
        if (rds_channel4collect.datas.size() > 0) {
            record_data_set rds;
            if (Integer.parseInt(rds_channel4collect.datas.get(0).get("protocol")) != 4) {
                sqlstr = "select value from dataItem2channel";
                sqlstr += String.format(" where channel_id=%d and slave_id=%d", channel_id, slave_id);
                sqlstr += String.format(" and register_type=%d", register_type);
                sqlstr += String.format(" and data_index>=%d", data_index);
                sqlstr += String.format(" order by data_index");
                sqlstr += String.format(" limit 0,%d", data_count);
                rds = drcdb.executeQuerySQL(sqlstr);
            } else {
                sqlstr = "select value from dataItem2channel";
                sqlstr += String.format(" where channel_id=%d", channel_id);
                sqlstr += String.format(" and data_index>=%d", data_index);
                sqlstr += String.format(" order by data_index");
                sqlstr += String.format(" limit 0,%d", data_count);
                rds = drcdb.executeQuerySQL(sqlstr);
            }
            return rds;
        } else {
            return null;
        }
    }

    public static record_data_set readLocalDataItem2(DRCDB drcdb, int channel_id, int data_index, int data_count) {
        String sqlstr = "select value from dataItem2channel";
        sqlstr += String.format(" where channel_id=%d ", channel_id);
        sqlstr += String.format(" and data_index>=%d", data_index);
        sqlstr += String.format(" order by data_index");
        sqlstr += String.format(" limit 0,%d", data_count);
        record_data_set rds = drcdb.executeQuerySQL(sqlstr);
        return rds;
    }

    public record_data_set read_dataItems_page(int channel_id,
                                               int pagesize, int page) {
        DRCDB db = this.drcdb;
        String sqlstr = "select d2c.dataItem2channel_id,d2c.channel_id,d2c.dataItem_id,d2c.label as d2cLabel,d4c.value,d4c.timestamp,d4c.label as d4cLabel from dataItem2channel d2c"
                + " left join dataItem4collect d4c on d2c.dataItem_id=d4c.dataItem_id";
        sqlstr += String.format(" where d2c.channel_id=%d ", channel_id);
        sqlstr += String.format(" order by d2c.dataItem2channel_id ");
        sqlstr += String.format(" limit %d,%d", pagesize * (page - 1), pagesize);
        record_data_set rds = db.executeQuerySQL(sqlstr);
        return rds;
    }

    /**
     * 关联WEB API列表
     * @param api_lists
     * @param drcdb
     */
    public static void associate_actions(List<DRHTTP_API> api_lists, DRCDB drcdb) throws ClassNotFoundException {
        @SuppressWarnings("unchecked")
        Class<?> CLS = (Class<dataItem2channel>) Class.forName("DRC.dataItem2channel");
        for (Method method : CLS.getDeclaredMethods()) {
            FJW_URI fjw_uri = method.getAnnotation(FJW_URI.class);
//            System.out.println(method.toString());
            if (fjw_uri != null) {
//                System.out.println(method.toString());
//                System.out.println(fjw_uri.value());
                try {
                    api_lists.add((DRHTTP_API) method.invoke(CLS, drcdb, fjw_uri.value()));
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @FJW_URI(value = "dataItem2channel/dataItem2channelList")
    public static DRHTTP_API dataItem2channelList(DRCDB drcdb, String URI) {
        return new DRHTTP_API(drcdb, URI) {
            @Override
            public String getResult(Map<String, Object> parameters, DR_SESSION drs) {
                String content = "";
                String channel_id = this.get_parameters(parameters, "channel_id");
                String page = this.get_parameters(parameters, "page");
                String limit = this.get_parameters(parameters, "limit");
                dataItem2channel d2c = new dataItem2channel(drcdb);
                record_data_set rds = d2c.read_dataItems_page(
                        Integer.parseInt(channel_id), Integer.parseInt(limit), Integer.parseInt(page)
                );
                for (Map<String, String> data : rds.datas) {
                    String dataItemId = data.get("dataItem_id");
                    for (channel4collect ch4c : channels4collect) {
                        for (dataItem4collect di4c : ch4c.ds4c) {
                            if (di4c.dataItem_id == Integer.parseInt(dataItemId)) {
                                data.put("value", String.valueOf(di4c.value));
                                data.put("timestamp", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(di4c.timestamp));
                            }
                        }
                    }
                }
                content = record_data_set.get_answer_json_str(0, "", rds);
                return content;
            }
        };
    }

    @FJW_URI(value = "dataItem2channel/dataItem2channelListNew")
    public static DRHTTP_API dataItem2channelListNew(DRCDB drcdb, String URI) {
        return new DRHTTP_API(drcdb, URI) {
            @Override
            public String getResult(Map<String, Object> parameters, DR_SESSION drs) {
                String content = "";
                String channel_id = this.get_parameters(parameters, "channel_id");
                String channel4collect_id = this.get_parameters(parameters, "channel4collect_id");
                record_data_set rds = new record_data_set();
                List<String> columns = new ArrayList<>();
                columns.add("channel_id");
                columns.add("dataItem_id");
                columns.add("slave_id");
                columns.add("register_type");
                columns.add("data_index");
                columns.add("address");
                columns.add("label");
                columns.add("data_type");
                columns.add("coefficient");
                columns.add("negation");
                columns.add("BCD");
                columns.add("ABS");
                columns.add("base_value");
                columns.add("introduction");
                columns.add("data_table");
                columns.add("field");
                columns.add("timestamp");
                columns.add("value");
                columns.add("create_time");
                rds.columns = columns;
                List<Map<String, String>> datas = new ArrayList<>();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                for (channel4collect ch4c : channels4collect) {
                    if (ch4c.channel_id == Integer.parseInt(channel4collect_id)) {
                        for (dataItem4collect d4c : ch4c.ds4c) {
                            if (d4c.dataItem_id != Integer.parseInt(channel_id)) {
                                Map<String, String> map = new HashMap<>();
                                map.put("channel_id", String.valueOf(d4c.channel_id));
                                map.put("dataItem_id", String.valueOf(d4c.dataItem_id));
                                map.put("slave_id", String.valueOf(d4c.slave_id));
                                map.put("register_type", String.valueOf(d4c.register_type));
                                map.put("data_index", String.valueOf(d4c.data_index));
                                map.put("address", String.valueOf(d4c.address));
                                map.put("label", d4c.label);
                                map.put("data_type", String.valueOf(d4c.data_type));
                                map.put("coefficient", String.valueOf(d4c.coefficient));
                                map.put("negation", String.valueOf(d4c.negation));
                                map.put("BCD", String.valueOf(d4c.BCD));
                                map.put("ABS", String.valueOf(d4c.ABS));
                                map.put("base_value", String.valueOf(d4c.base_value));
                                map.put("introduction", d4c.introduction);
                                map.put("data_table", d4c.data_table);
                                map.put("field", d4c.field);
                                map.put("value", String.valueOf(d4c.value));
                                map.put("timestamp", sdf.format(d4c.timestamp));
                                map.put("create_time", String.valueOf(d4c.create_time));
                                datas.add(map);
                            }
                        }
                    }
                }
                rds.datas = datas;
                rds.count = datas.size();
                content = record_data_set.get_answer_json_str(0, "", rds);
                return content;
            }
        };
    }

    @FJW_URI(value = "dataItem2channel/dataItem2channelList2")
    public static DRHTTP_API dataItem2channelList2(DRCDB drcdb, String URI) {
        return new DRHTTP_API(drcdb, URI) {
            @Override
            public String getResult(Map<String, Object> parameters, DR_SESSION drs) {
                String content = "";
                String channel_id = this.get_parameters(parameters, "channel_id");
                String channel4collect_id = this.get_parameters(parameters, "channel4collect_id");
                String sqlstr = "select * from dataItem4collect ";
                sqlstr += " where channel_id=" + channel4collect_id;
                sqlstr += " and dataItem_id not in (\r\n" + //
                        "\t select dataItem_id \r\n" + //
                        "\t from dataItem2channel\r\n" + //
                        "\t where channel_id=" + channel_id + "\r\n" + //
                        "\t )";
                sqlstr += " order by address";
                record_data_set rds = drcdb.executeQuerySQL(sqlstr);
                content = record_data_set.get_answer_json_str(0, "", rds);
                return content;
            }
        };
    }

    @FJW_URI(value = "dataItem2channel/add_dataItem2channel")
    public static DRHTTP_API add_dataItem2channel(DRCDB drcdb, String URI) {
        return new DRHTTP_API(drcdb, URI) {
            @Override
            public String getResult(Map<String, Object> parameters, DR_SESSION drs) {
                String content = "";
                String channel_id = this.get_parameters(parameters, "channel_id");
                String channel4collect_id = this.get_parameters(parameters, "channel4collect_id");
                String dataItem_id = this.get_parameters(parameters, "dataItem");
                String label = this.get_parameters(parameters, "label");
                boolean result = false;
                String sqlstr;
                sqlstr = "select count(1) as ct from dataItem2channel";
                sqlstr += String.format(" where channel_id=%s", channel_id);
                sqlstr += String.format(" and dataItem_id=%s", dataItem_id);
                record_data_set rds = drcdb.executeQuerySQL(sqlstr);
                if (Integer.parseInt(rds.datas.get(0).get("ct")) == 0) {
                    Date now = new Date();
                    SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    sqlstr = "insert into dataItem2channel(channel_id,dataItem_id,label,create_time) ";
                    sqlstr += String.format(" values(%s,%s,'%s','%s')",
                            channel_id, dataItem_id, label, ft.format(now));
                    result = drcdb.executeUpdateSQL(sqlstr);
                }
                if (result) {
                    content = dr_ajax_answer.sample_json_string(0, "");
                } else {
                    content = dr_ajax_answer.sample_json_string(1, "保存失败！");
                }
                return content;
            }
        };
    }

    @FJW_URI(value = "dataItem2channel/del_dataItem2channel")
    public static DRHTTP_API del_dataItem2channel(DRCDB drcdb, String URI) {
        return new DRHTTP_API(drcdb, URI) {
            @Override
            public String getResult(Map<String, Object> parameters, DR_SESSION drs) {
                String content = "";
                String dataItem2channel_id = this.get_parameters(parameters, "dataItem2channel_id");
                boolean result = false;
                String sqlstr = "delete from dataItem2channel ";
                sqlstr += String.format(" where dataItem2channel_id=%s",
                        dataItem2channel_id);
                result = drcdb.executeUpdateSQL(sqlstr);
                if (result) {
                    content = dr_ajax_answer.sample_json_string(0, "");
                } else {
                    content = dr_ajax_answer.sample_json_string(1, "删除失败！");
                }
                return content;
            }
        };
    }

    @FJW_URI(value = "dataItem2channel/upload_dataItem_table")
    public static DRHTTP_API upload_dataItem_table(DRCDB drcdb, String URI) {
        return new DRHTTP_API(drcdb, URI) {
            @Override
            public String getResult(Map<String, Object> parameters, DR_SESSION drs) {
                String content = "";
                Object upload_file = this.get_parameterItem(this.parameters, "upload_file");
                String channel_id = ((HttpRequestFormResolver.ParamItem) (this.get_parameterItem(this.parameters, "channel_id"))).getVal();
                boolean result = false;
                int cell_process_index = 0;
                if (upload_file != null) {
                    InputStream fis = null;
                    try {
                        HttpRequestFormResolver.ParamItem pi = (HttpRequestFormResolver.ParamItem) upload_file;
                        System.out.println(pi.getFilename());
                        File excel_file = new File("./upload/" + pi.getFilename());
                        fis = new FileInputStream(excel_file.getAbsolutePath());
                        HSSFWorkbook wb = new HSSFWorkbook(fis, true);

                        HSSFSheet ws = wb.getSheetAt(0);
                        HSSFRow row;
                        HSSFCell cell;
                        int cell_channel_id, cell_dataItem_address;
                        HSSFRichTextString cell_label;
                        int max_data_count = 10000;
                        try {
                            result = true;
                            for (int i = 1; i < max_data_count; i++) {
                                row = ws.getRow(i);
                                cell = row.getCell(1);
                                cell_channel_id = (int) cell.getNumericCellValue();
                                cell = row.getCell(2);
                                cell_label = cell.getRichStringCellValue();
                                cell = row.getCell(3);
                                cell_dataItem_address = (int) cell.getNumericCellValue();
                                if (add_dataItem2channel2(drcdb, channel_id,
                                        cell_channel_id + "",
                                        cell_label.toString(),
                                        cell_dataItem_address + "")) {
                                    cell_process_index++;
                                }
                                System.out.println(String.format("channel_id=%s,label=%s,dataItem_id=%s",
                                        cell_channel_id, cell_label.getString(), cell_dataItem_address
                                ));
                            }

                        } catch (Exception e) {
                            System.out.println("excel read finished!");
                        }
                        fis.close();
                    } catch (Exception e) {
                        System.out.println(e.getLocalizedMessage());
                    }
                }
                content = dr_ajax_answer.sample_json_string(0, "");
                if (result) {
                    content = dr_ajax_answer.sample_json_string(0, String.format("点表读取成功，保存数据[%d]条", cell_process_index));
                } else {
                    content = dr_ajax_answer.sample_json_string(1, "上传失败！");
                }
                return content;
            }
        };
    }

    /**
     * 添加服务通道点表
     *
     * @param drcdb              数据库句柄
     * @param channel_id         服务通道ID
     * @param channel4collect_id 采集通道ID
     * @param label              标签名
     * @param address            数据点地址
     * @return 添加是否成功
     */
    private static boolean add_dataItem2channel2(DRCDB drcdb,
                                                 String channel_id, String channel4collect_id, String label, String address) {
        boolean result = false;
        String sqlstr;
        record_data_set ds;
        sqlstr = "select dataItem_id from dataItem4collect ";
        sqlstr += String.format("where channel_id=%s ", channel4collect_id);
        sqlstr += String.format("and address=%s limit 0,1", address);
        ds = drcdb.executeQuerySQL(sqlstr);
        if (ds.count > 0) {
            Date now = new Date();
            SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dataItem_id = ds.datas.get(0).get("dataItem_id");
            sqlstr = "select * from dataItem2channel ";
            sqlstr += String.format("where channel_id=%s and dataItem_id=%s", channel_id, dataItem_id);
            ds = drcdb.executeQuerySQL(sqlstr);
            if (ds.count == 0) {
                sqlstr = "insert into dataItem2channel(channel_id,dataItem_id,label,create_time) ";
                sqlstr += String.format("values(%s,%s,'%s','%s')", channel_id, dataItem_id, label, ft.format(now));
                result = drcdb.executeUpdateSQL(sqlstr);
            } else {
                sqlstr = "update dataItem2channel ";
                sqlstr += String.format("set label='%s' ", label);
                sqlstr += String.format("where channel_id=%s ", channel_id);
                sqlstr += String.format("and dataItem_id=%s ", dataItem_id);
                result = drcdb.executeUpdateSQL(sqlstr);
            }
        }
        return result;
    }

    public static void main(String[] args) {
        DRCDB drcdb = new DRCDB();
        // String result=dataItem2channel.dataItem2channelList(drcdb,"1","0", "20");
        // System.out.println(result);

    }
}